﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

namespace Utils
{
    public class DBConnection
    {
        private SqlDataAdapter myAdapter;
        private SqlConnection conn;

        /// <constructor>
        /// Initialise Connection
        /// </constructor>
        public DBConnection()
        {
            myAdapter = new SqlDataAdapter();
            conn = new SqlConnection(ConfigurationManager.ConnectionStrings
                    ["onlineAutionConnectionString"].ConnectionString);
        }

        /// <method>
        /// Open Database Connection if Closed or Broken
        /// </method>
        private SqlConnection openConnection()
        {
            if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken )
            {
                conn.Open();
                
            }
            return conn;
        }

        public void CloseConnectData()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
                //conn.Dispose();
                //myAdapter.Dispose();
                //conn = null;
            }
        }

        /// <method>
        /// Select Query : THUC THI CAU LENH SELECT CO THAM SO
        /// </method>
        public DataTable executeSelectQueryWithParams(String _query, SqlParameter[] sqlParameter)
        {
            SqlCommand myCommand = new SqlCommand();
            DataTable dataTable = new DataTable();
            dataTable = null;
            DataSet ds = new DataSet();
            try
            {
                myCommand.Connection = openConnection();
                myCommand.CommandText = _query;
                myCommand.Parameters.AddRange(sqlParameter);
                myCommand.ExecuteNonQuery();                
                myAdapter.SelectCommand = myCommand;
                myAdapter.Fill(ds);
                dataTable = ds.Tables[0];
            }
            catch (SqlException e)
            {
                Console.Write("Error - Connection.executeSelectQuery - Query: " + _query + " \nException: " + e.StackTrace.ToString());
                return null;
            }
            finally
            {
                CloseConnectData();
            }
            return dataTable;
        }

    //*******************************************************************************************************************************************


  
    
    //***************THUC THI CAU LENH CO THAM SO VA KET QUA TRA VE CHI CO 1 DONG , 1 COT ***********************************************


    public int executeSelectQueryReturnIntWithParams(String query, SqlParameter[] sqlParameter)
    {
        SqlCommand myCommand = new SqlCommand();
        int name=0;
        try
        {
            myCommand.Connection = openConnection();
            myCommand.CommandText = query;
            myCommand.Parameters.AddRange(sqlParameter);
            name=Convert.ToInt32( myCommand.ExecuteScalar());
            
        }
        catch (SqlException e)
        {
            Console.Write("Error - Connection.executeSelectQuery - Query: " + query + " \nException: " + e.StackTrace.ToString());
            //return null;
        }
        finally
        {
            CloseConnectData();
        }
        return name;
    }




    public string executeSelectQueryReturnStringWithParams(String _query, SqlParameter[] sqlParameter)
    {
        SqlCommand myCommand = new SqlCommand();
        DataTable dataTable = new DataTable();
        dataTable = null;
        DataSet ds = new DataSet();
        string str = "";
        try
        {
            myCommand.Connection = openConnection();
            myCommand.CommandText = _query;
            myCommand.Parameters.AddRange(sqlParameter);
            myCommand.ExecuteNonQuery();
            myAdapter.SelectCommand = myCommand;
            myAdapter.Fill(ds);
            dataTable = ds.Tables[0];
            str = dataTable.Rows[0][0].ToString();
        }
        catch (SqlException e)
        {
            Console.Write("Error - Connection.executeSelectQuery - Query: " + _query + " \nException: " + e.StackTrace.ToString());
            return null;
        }
        finally
        {
            CloseConnectData();
        }
        return str;
    }









    //******************************************************************************************************************

    //*******************************THUC THI CAU LENH KO CO THAM SO VA KET QUA TRA VE CHI CO 1 DONG , 1 COT   ******************************************************************************


    public int executeSelectQueryReturnInt(String query)
    {
        SqlCommand myCommand = new SqlCommand();
        int ID = 0;
        try
        {
            myCommand.Connection = openConnection();
            myCommand.CommandText = query;
            ID = Convert.ToInt32(myCommand.ExecuteScalar());

        }
        catch (SqlException e)
        {
            Console.Write("Error - Connection.executeSelectQuery - Query: " + query + " \nException: " + e.StackTrace.ToString());
            //return null;
        }
        finally
        {
            CloseConnectData();
        }
        return ID;
    }



    //******************************************************************************************************************
    


    //****************************THUC THI CAU LENH SELECT KHONG CO THAM SO*********************************************

    public DataTable executeSelectQuery(String _query)
    {
        SqlCommand myCommand = new SqlCommand();
        DataTable dataTable = new DataTable();
        dataTable = null;
        DataSet ds = new DataSet();
        try
        {
            myCommand.Connection = openConnection();
            myCommand.CommandText = _query;
            myCommand.ExecuteNonQuery();
            myAdapter.SelectCommand = myCommand;
            myAdapter.Fill(ds);
            dataTable = ds.Tables[0];
        }
        catch (SqlException e)
        {
            Console.Write("Error - Connection.executeSelectQuery - Query: " + _query + " \nException: " + e.StackTrace.ToString());
            return null;
        }
        finally
        {
            CloseConnectData();
        }
        return dataTable;
    }


    //****************************************************************************************


    //*****************THUC THI CAU LENH SELECT BANG SqlDataReader KHONG CO THAM SO ******************


    public SqlDataReader executeSelectQueryBySQLDataReader(String _query)
    {
        SqlCommand myCommand = new SqlCommand();
        SqlDataReader dr;
        try
        {
            myCommand.Connection = openConnection();
            myCommand.CommandText = _query;
            dr = myCommand.ExecuteReader();
        }
        catch (SqlException e)
        {
            Console.Write("Error - Connection.executeSelectQuery - Query: " + _query + " \nException: " + e.StackTrace.ToString());
            return null;
        }
        finally
        {
            CloseConnectData();
        }
        return dr;
    }


    //****************************************************************************************


    //***********************************THUC THI CAU LENH SELECT BANG SqlDataReader CO THAM SO


    public SqlDataReader executeSelectQueryBySQLDataReaderWithParams(String _query, SqlParameter[] sqlParameter)
    {
        SqlCommand myCommand = new SqlCommand();
        SqlDataReader dr;
        try
        {
            myCommand.Connection = openConnection();
            myCommand.CommandText = _query;
            myCommand.Parameters.AddRange(sqlParameter);
            dr = myCommand.ExecuteReader();
            
        }
        catch (SqlException e)
        {
            Console.Write("Error - Connection.executeSelectQuery - Query: " + _query + " \nException: " + e.StackTrace.ToString());
            return null;
        }
        finally
        {
            CloseConnectData();
        }
        return dr;
    }


    //****************************************************************************************


    //************************************* THUC THI CAU LENH INSERT   **************************************************


    /// <method>
    /// Insert Query
    /// </method>
    public bool executeInsertQueryNoVar(String _query)
    {
        SqlCommand myCommand = new SqlCommand();
        try
        {
            myCommand.Connection = openConnection();
            myCommand.CommandText = _query;
            myAdapter.InsertCommand = myCommand;
            myCommand.ExecuteNonQuery();
        }
        catch (SqlException e)
        {
            Console.Write("Error - Connection.executeInsertQuery - Query: " + _query + " \nException: \n" + e.StackTrace.ToString());
            return false;
        }
        finally
        {
            CloseConnectData();
        }
        return true;
    }


    //****************************************************************************************

    //************************************* THUC THI CAU LENH INSERT   **************************************************


        /// <method>
        /// Insert Query
        /// </method>
        public bool executeInsertQuery(String _query, SqlParameter[] sqlParameter)
        {
            bool flag = true;
            SqlCommand myCommand = new SqlCommand();
            try
            {
                myCommand.Connection = openConnection();
                myCommand.CommandText = _query;
                myCommand.Parameters.AddRange(sqlParameter);
                myAdapter.InsertCommand = myCommand;
                myCommand.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                flag = false;
                Console.Write("Error - Connection.executeInsertQuery - Query: " + _query + " \nException: \n" + e.StackTrace.ToString());
                throw new Exception("executeInsertQuery : "  + e.ToString());
            }
            finally
            {
                CloseConnectData();
                myCommand.Parameters.Clear();
            }
            return flag;
        }


        //****************************************************************************************



        //****************************THUC THI CAU LENH UPDATE ************************************************************

        /// <method>
        /// Update Query
        /// </method>
        public bool executeUpdateQuery(String _query, SqlParameter[] sqlParameter)
        {
            SqlCommand myCommand = new SqlCommand();
            try
            {
                myCommand.Connection = openConnection();
                myCommand.CommandText = _query;
                myCommand.Parameters.AddRange(sqlParameter);
                myAdapter.UpdateCommand = myCommand;
                myCommand.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                Console.Write("Error - Connection.executeUpdateQuery - Query: " + _query + " \nException: " + e.StackTrace.ToString());
                return false;
            }
            finally
            {
              
                CloseConnectData();
                myCommand.Parameters.Clear();

            }
            return true;
        }


        //****************************************************************************************




        //************************ THUC THI CAU LENH DELETE   ****************************************************************

        public bool executeDeleteQuery(String _query, SqlParameter[] sqlParameter)
        {
            SqlCommand myCommand = new SqlCommand();
            try
            {
                myCommand.Connection = openConnection();
                myCommand.CommandText = _query;
                myCommand.Parameters.AddRange(sqlParameter);
                myAdapter.DeleteCommand = myCommand;
                myCommand.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                Console.Write("Error - Connection.executeUpdateQuery - Query: " + _query + " \nException: " + e.StackTrace.ToString());
                return false;
            }
            finally
            {
                CloseConnectData();
                myCommand.Parameters.Clear();
            }
            return true;
        }

    }
}
